﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using ObjOrganizer;

namespace DLOrganizer
{
    public class DLCalendar : Utilities
    {
        #region Constructors
        public DLCalendar()
        {
            Database = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        }
        #endregion

        #region Calendar
        public DataTable GET_FullCalendar(int UserId, int Month, int Year)
        {
            try
            {
                StoredProcedure = "GET_Calendar";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Month", Month);
                Parameters.Add("@Year", Year);
                DataTable Calendar = SQLExecuteDatatable();
                return Calendar;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public Dictionary<string, string> GET_Calendar(int UserId, int Month, int Year)
        {
            try
            {
                Dictionary<string, string> List = new Dictionary<string, string>();
                StoredProcedure = "GET_Calendar";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Month", Month);
                Parameters.Add("@Year", Year);
                SqlDataReader Calendar = SQLExecuteDataReader();
                while (Calendar.Read())
                {
                    DateTime Dbdate = Convert.ToDateTime(Calendar["Date"].ToString());
                    string date = Dbdate.Day + "-" + Dbdate.Month + "-" + Dbdate.Year;
                    List.Add(date, Calendar["Color"].ToString());
                }
                Calendar.Close();
                return List;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public List<string> GET_CalendarDay(int UserId, int Day, int Month, int Year)
        {
            try
            {
                List<string> List = new List<string>();
                StoredProcedure = "GET_CalendarDay";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Day", Day);
                Parameters.Add("@Month", Month);
                Parameters.Add("@Year", Year);
                SqlDataReader Calendar = SQLExecuteDataReader();
                while (Calendar.Read())
                {                   
                    List.Add(Calendar["Color"].ToString());
                    List.Add(Calendar["Msg"].ToString());
                    List.Add(Calendar["CalendarId"].ToString());
                }
                Calendar.Close();
                return List;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void ADD_Calendar(int UserId, DateTime Date, string Color, string Msg)
        {
            try
            {
                StoredProcedure = "ADD_Calendar";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Date", Date);
                Parameters.Add("@Color", Color);
                Parameters.Add("@Msg", Msg);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        public void DEL_Calendar(int CalendarId)
        {
            try
            {
                StoredProcedure = "DEL_Calendar";
                Parameters.Clear();
                Parameters.Add("@CalendarId", CalendarId);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }

    public class DLMovies : Utilities
    {
        #region Constructors

        public DLMovies()
        {
            Database = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        }

        #endregion Constructors

        #region Movies

        public DataTable GET_Movies()
        {
            try
            {
                StoredProcedure = "GET_Movies";
                DataTable Movies = SQLExecuteDatatable();
                return Movies;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GET_MoviesByUser(int UserId)
        {
            try
            {
                StoredProcedure = "GET_MoviesByUserId";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                DataTable Movies = SQLExecuteDatatable();
                return Movies;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GET_MovieById(int MovieId)
        {
            try
            {
                StoredProcedure = "GET_MovieById";
                Parameters.Clear();
                Parameters.Add("@MovieId", MovieId);
                DataTable Movies = SQLExecuteDatatable();
                return Movies;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ADD_Movie(int UserId, string Name, string Type, string ViewedDate, string Score)
        {
            try
            {
                StoredProcedure = "ADD_Movie";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Name", Name);
                Parameters.Add("@Type", Type);
                Parameters.Add("@ViewedDate", ViewedDate);
                Parameters.Add("@Score", Score);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UPD_Movie(int MovieId, string Name, string Type, string ViewedDate, string Score)
        {
            try
            {
                StoredProcedure = "UPD_Movie";
                Parameters.Clear();
                Parameters.Add("@MovieId", MovieId);
                Parameters.Add("@Name", Name);
                Parameters.Add("@Type", Type);
                Parameters.Add("@ViewedDate", ViewedDate);
                Parameters.Add("@Score", Score);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DEL_Movie(int MovieId)
        {
            try
            {
                StoredProcedure = "DEL_Movie";
                Parameters.Clear();
                Parameters.Add("@MovieId", MovieId);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion Movies
    }

    public class DLTvShows : Utilities
    {
        #region Constructors

        public DLTvShows()
        {
            Database = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        }

        #endregion Constructors

        #region TvShows

        public DataTable GET_TvShows()
        {
            try
            {
                StoredProcedure = "GET_TvShows";
                DataTable TvShows = SQLExecuteDatatable();
                return TvShows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GET_TvShowsByUser(int UserId)
        {
            try
            {
                StoredProcedure = "GET_TvShowsByUserId";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                DataTable TvShows = SQLExecuteDatatable();
                return TvShows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GET_TvShowById(int TvShowId)
        {
            try
            {
                StoredProcedure = "GET_TvShowById";
                Parameters.Clear();
                Parameters.Add("@TvShowId", TvShowId);
                DataTable TvShows = SQLExecuteDatatable();
                return TvShows;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ADD_TvShow(int UserId, string Name, int EpisodeNumber, int SeasonNumber, int LastViewedEpisode, string ReleaseDay, string Score)
        {
            try
            {
                StoredProcedure = "ADD_TvShow";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Name", Name);
                Parameters.Add("@SeasonNumber", SeasonNumber);
                Parameters.Add("@EpisodeNumber", EpisodeNumber);
                Parameters.Add("@LastViewedEpisode", LastViewedEpisode);
                Parameters.Add("@ReleaseDay", ReleaseDay);
                Parameters.Add("@Score", Score);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UPD_TvShow(int TvShowId, string Name, int EpisodeNumber, int SeasonNumber, int LastViewedEpisode, string ReleaseDay, string Score)
        {
            try
            {
                StoredProcedure = "UPD_TvShow";
                Parameters.Clear();
                Parameters.Add("@TvShowId", TvShowId);
                Parameters.Add("@Name", Name);
                Parameters.Add("@SeasonNumber", SeasonNumber);
                Parameters.Add("@EpisodeNumber", EpisodeNumber);
                Parameters.Add("@LastViewedEpisode", LastViewedEpisode);
                Parameters.Add("@ReleaseDay", ReleaseDay);
                Parameters.Add("@Score", Score);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DEL_TvShow(int TvShowId)
        {
            try
            {
                StoredProcedure = "DEL_TvShow";
                Parameters.Clear();
                Parameters.Add("@TvShowId", TvShowId);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion TvShows
    }

    public class DLBooks : Utilities
    {
        #region Constructors

        public DLBooks()
        {
            Database = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        }

        #endregion Constructors

        #region Books

        public DataTable GET_Books()
        {
            try
            {
                StoredProcedure = "GET_Books";
                DataTable Books = SQLExecuteDatatable();
                return Books;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GET_BooksByUser(int UserId)
        {
            try
            {
                StoredProcedure = "GET_BooksByUserId";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                DataTable Books = SQLExecuteDatatable();
                return Books;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public DataTable GET_BookById(int BookId)
        {
            try
            {
                StoredProcedure = "GET_BookById";
                Parameters.Clear();
                Parameters.Add("@BookId", BookId);
                DataTable Books = SQLExecuteDatatable();
                return Books;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ADD_Book(int UserId, string Name, string Type, bool Completed)
        {
            try
            {
                StoredProcedure = "ADD_Book";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@Name", Name);
                Parameters.Add("@Type", Type);
                Parameters.Add("@Completed", Completed);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UPD_Book(int BookId, string Name, string Type, bool Completed)
        {
            try
            {
                StoredProcedure = "UPD_Book";
                Parameters.Clear();
                Parameters.Add("@BookId", BookId);
                Parameters.Add("@Name", Name);
                Parameters.Add("@Type", Type);
                Parameters.Add("@Completed", Completed);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void DEL_Book(int BookId)
        {
            try
            {
                StoredProcedure = "DEL_Book";
                Parameters.Clear();
                Parameters.Add("@BookId", BookId);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion Books
    }

    public class DLUsers : Utilities
    {
        #region Constructors

        public DLUsers()
        {
            Database = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        }

        #endregion Constructors

        #region Users

        public int GET_User(string UserName, string Password)
        {
            try
            {
                StoredProcedure = "GET_User";
                Parameters.Clear();
                Parameters.Add("@UserName", UserName);
                Parameters.Add("@PassWord", Password);
                SqlDataReader User = SQLExecuteDataReader();
                int UserId = 0;
                if (User.Read())
                {
                    UserId = (int)User["UserId"];
                }
                User.Close();
                CloseConnection();
                return UserId;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public ObjUser GET_UserInfoById(int UserId)
        {
            try
            {
                ObjUser UserInfo = new ObjUser();
                StoredProcedure = "GET_UserInfoById";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                SqlDataReader User = SQLExecuteDataReader();
                if (User.Read())
                {
                    UserInfo.UserId = (int)User["UserId"];
                    UserInfo.Name = User["Name"].ToString();
                    UserInfo.Password = User["Password"].ToString();
                }
                User.Close();
                CloseConnection();
                return UserInfo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void ADD_User(string Name, string Password)
        {
            try
            {
                StoredProcedure = "ADD_User";
                Parameters.Clear();
                Parameters.Add("@Name", Name);
                Parameters.Add("@Password", Password);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void UPD_User(string Name, string Password, int UserId)
        {
            try
            {
                StoredProcedure = "UPD_User";
                Parameters.Clear();
                Parameters.Add("@UserName", Name);
                Parameters.Add("@Password", Password);
                Parameters.Add("@UserId", UserId);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion Users
    }

    public class DLReports : Utilities
    {
        #region Constructors

        public DLReports()
        {
            Database = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
        }

        #endregion Constructors

        #region Reports

        public void ADD_Report(int UserId, string ReportType, string Message)
        {
            try
            {
                StoredProcedure = "ADD_Report";
                Parameters.Clear();
                Parameters.Add("@UserId", UserId);
                Parameters.Add("@ReportType", ReportType);
                Parameters.Add("@Message", Message);
                SQLExecuteQuery();
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion
    }    
}